package com.girls.test;

import com.girls.dao.*;
import com.girls.model.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class Egotistic {
    //1.根据全局配置文件创建SqlSessionFactory
    public SqlSessionFactory initSqlSessionFactory() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory;
    }

    private static SqlSessionFactory sqlSessionFactory;

    {
        try {
            sqlSessionFactory = initSqlSessionFactory();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void TestSelect() throws IOException {
        //2.通过SqlSessionFactory开启数据库会话
        SqlSession sqlSession= sqlSessionFactory.openSession();

        //3.通过SqlSession操作数据库
        try {
            //获取到的是EmployeeDao接口的代理对象，由mybatis自动创建
            EmployeeDao employeeDao=sqlSession.getMapper(EmployeeDao.class);
            Employee employee = employeeDao.getEmployeeByID(2);
            System.out.println(employee.toString());
            System.out.println(employeeDao.getClass());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test   //---------------插入----------------
    public void TestInsert() throws IOException {
        //2.通过SqlSessionFactory开启数据库会话
        SqlSession sqlSession= sqlSessionFactory.openSession(true);

        //3.通过SqlSession操作数据库
        try {
            EmployeeDao employeeDao=sqlSession.getMapper(EmployeeDao.class);
            Employee employee = new Employee(null, "yangdezhi", 1, "sb@s.com");
            int i = employeeDao.insertEmployee(employee);
            System.out.println("影响"+i+"行");
            System.out.println("插入id为："+employee.getId());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //增删改要手动提交，或者在openSession时设置为true
//            sqlSession.commit();
            sqlSession.close();
        }
    }
    @Test
    public void TestDelete() throws IOException {
        //2.通过SqlSessionFactory开启数据库会话
        SqlSession sqlSession= sqlSessionFactory.openSession(true);

        //3.通过SqlSession操作数据库
        try {
            EmployeeDao employeeDao=sqlSession.getMapper(EmployeeDao.class);
            boolean i = employeeDao.deleteEmployee(5);
            System.out.println("影响"+i+"行");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //增删改要手动提交，或者在openSession时设置为true
//            sqlSession.commit();
            sqlSession.close();
        }
    }
    @Test
    public void testAnnotation(){
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        EmployeeDaoAnnotation mapper = sqlSession.getMapper(EmployeeDaoAnnotation.class);
        Employee employee = mapper.getEmployeeByID(1);
        System.out.println(employee);
    }
    @Test//多参数
    public void testSelect2() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            Employee employee = employeeDao.getEmployeeByIDAndEmpName(1, "TSwei");
            System.out.println("查询结果： "+employee.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test//MAP
    public void testSelect3() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            HashMap<String, Object> map = new HashMap<String, Object>();
            map.put("gender", 1);
            map.put("empName", "xiaochuan");
            map.put("tableName","t_employee");
            Employee employee = employeeDao.getEmployeeByMap(map);
            System.out.println("查询结果： "+employee.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test//查询*
    public void testSelect4() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            List<Employee> allEmployees = employeeDao.getAllEmployees();
            for (Employee employee:allEmployees
                 ) {
                System.out.println(employee.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test//查询1条返回map
    public void testSelect5() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            Map<String, Object> employeeByIDReturnMap = employeeDao.getEmployeeByIDReturnMap(2);
            System.out.printf(employeeByIDReturnMap.toString());
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test//查询多条返回map
    public void testSelect6() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            Map<Integer, Employee> map = employeeDao.getAllEmployeesReturnMap();
            System.out.printf(map.toString());

            Employee employee = map.get(1);
            System.out.printf(employee.toString());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test//resultMap自定义封装规则
    public void testSelect7() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            CatDao mapper = sqlSession.getMapper(CatDao.class);
            Cat cat = mapper.getCatByID(1);
            System.out.println(cat.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test//关联查询，一对一
    public void testSelect8() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            KeyDao mapper = sqlSession.getMapper(KeyDao.class);
            Key key = mapper.getKeyByID(2);
            System.out.println(key.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    /*
    一对多。外键放置在多的那张表。
    我大清有14亿人，对应一个爱新觉罗·近平。所以每一个中国人都要铭记他们的皇帝的名字是近平，
    总不可能让我习大帝一个人记14亿头清人每个人的名字吧？贱奴才要造反？？？
    然而如果是多对多，一条裙子版型很多小婊子穿，一个小婊子不可能只穿一条裙子
    （比如我雨薇大公主不止喜穿碎花洋裙，发起骚来齐B包臀一条条的呢呢呢呢~）
    所以这时候需要创建 裙-婊 中！间！表！，来保存每一条多对多的映射
     */
    public void testSelect9() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            ChainDao mapper = sqlSession.getMapper(ChainDao.class);
            Chain chain= mapper.getChainByID(3);
            System.out.println("该锁对应钥匙如下：");
            List<Key> keys=chain.getKeyList();
            for (Key key:keys
                 ) {
                System.out.println(key);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test//分步查询,懒加载
    public void testSelect10() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            KeyDao sqlSessionMapper = sqlSession.getMapper(KeyDao.class);
            Key key = sqlSessionMapper.simKeyByID(1);
            System.out.println(key.getKeyName());
            System.out.println(key);
//            Thread.sleep(3000);
//            System.out.println(key.getChain().getChainName());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test//分步查询，懒加载，1对多，照样秀
    public void testSelect11() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            ChainDao sqlSessionMapper = sqlSession.getMapper(ChainDao.class);
            Chain chain = sqlSessionMapper.lazyChainByID(3);
            System.out.println(chain.getChainName());
            List<Key> keyList = chain.getKeyList();
            for (Key key:keyList
                 ) {
                System.out.println(key);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    /*
    然而工作中咱们用连接查询、子查询，不用分步，因为分步会多次调用数据库，为数据库带来过大的
    负担
    ......
     */


    @Test
    /*
                                  动   态   S   Q   L
     */
    public void dynamicSQL1() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            TeacherDao sqlSessionMapper = sqlSession.getMapper(TeacherDao.class);
            Teacher teacher = new Teacher();
//            teacher.setId(1);
            teacher.setTeachername("%e%");
            teacher.setBirthday(new Date());
//            模糊查询
            List<Teacher> teachers = sqlSessionMapper.getTeachersByCondition(teacher);
            for (Teacher teacher1:teachers
                 ) {
                System.out.println(teacher1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    /*
            for each 标签
     */
    public void dynamicSQL2() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            TeacherDao sqlSessionMapper = sqlSession.getMapper(TeacherDao.class);
            List<Teacher> teachers = sqlSessionMapper.getTeachersByIDRange(Arrays.asList(1, 2, 3, 4, 5, 6, 7));
            for (Teacher teacher:teachers
            ) {
                System.out.println(teacher);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    /*
            choose分支
     */
    public void dynamicSQL3() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            TeacherDao sqlSessionMapper = sqlSession.getMapper(TeacherDao.class);
            Teacher teacher = new Teacher();
//            teacher.setId(1);
//            teacher.setTeachername("Erin Moriarty");
//            teacher.setBirthday(new Date());
            List<Teacher> teachers = sqlSessionMapper.getTeachersByConditionChoose(teacher);
            for (Teacher teacher0:teachers
            ) {
                System.out.println(teacher0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
    @Test
    /*
    set实现动态更新
     */
    public void dynamicSQL4() {
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            TeacherDao sqlSessionMapper = sqlSession.getMapper(TeacherDao.class);
            Teacher teacher = new Teacher();
            teacher.setId(5);
            teacher.setTeachername("李萱诗");
            int i = sqlSessionMapper.updateTeacher(teacher);
            System.out.println("受影响："+i+"行");
            sqlSession.commit();
            System.out.println(sqlSessionMapper.getTeacherByID(3));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }
}
